Sum*It Manual - Functions

Function index


Spreadsheet Functions

CELL(row,column)
 Returns the value of the cell in row row and column column. If row is 1 and column is 1 then the result will be the value of A1.
COLUMN(range)
 Returns the number of the left most cell in range. So COLUMN(C2..D4) will return 3.
DOCUMENT
 Returns the name of the current document.
HINDEX(value,row)
 Returns the offset of value in the range row. Row must be a range with incrementing values, since HINDEX will return the first offset starting from the left side which is either exactly the same as or greater then value. If value is found in the first cell, the result will be one (1).
HLOOKUP(value,row,offset)
 Looks for value in row, starting at the left edge. When it finds value or the first value greater than value it skips offset cells downward and returns the value found in that cell.
NCOLS(range)
 Returns the width of range. NCOLS(C3..D4) returns 2.
NROWS(range)
 Returns the height of range. NROWS(C3..D4) returns 2.
NUMPAGES
 Returns the amount of pages that the document consists of.
PAGE
 Returns the number of the page on which the cell containing this function is situated.
ROW(range)
 Returns the rownumber of the topmost cell in range. If range is C3..D4 then the result will be 3.
VINDEX(value,column)
 Returns the offset of value in the range column. Column must be a range with incrementing values, since VINDEX will return the first offset starting from the top which is either exactly the same as or greater then value. If value is found in the first cell, the result will be one (1).
VLOOKUP(value,column,offset)
 Looks for value in column, starting at the top. When it finds value or the first value greater than value it skips offset cells to the right and returns the value found in that cell.

Date/Time Functions

DATE(year,month,day)
 Returns the date calculated from year, month and day.
DAY(date)
 Returns the number of the day, of date. This is the number of days passed in the month.
DOW(date)
 Returns the number of the day in the week date is in. Sunday is day one, Monday is day two, etc...
HOUR(date)
 Returns the hour part of date.
MINUTE(date)
 Returns the minute part of date.
MONTH(date)
 Returns the month date is in. Januari is one.
NOW
 Returns the current date and time.
SECOND
 Returns the seconds part of date.
TIME(hour,minute,second)
 Returns the time constructed from hour, minute and second.
YEAR
 Returns the year date is in.

Financial Functions

ANNUITY(rate,periods)
 Returns the annuity determined by rate and periods.
The formula used is:
(1 - (1 + rate)-periods) / rate.
COMPOUND(rate,periods)
 Returns the compound determined by rate and periods.
The formula used is:
(1 + rate)periods
DB(inv,term,mult,period)
 Declining Balance Depreciation. Calculates the depreciation amount for a specific period from the original investment, the number of time periods over which to depreciate and the straight-line rate multiplier.
The formula used is:
inv * mult / term * (1 - mult / term)(period - 1)
FV(pmt,rate,periods)
 Future Value. Returns the future value based on the amount of interest earned during periodic payments. It is assumed that payments are made at the end of each period. Multiply the result by (1 + rate) to get the FV if payments are made at the beginning of each period.
The formula used is:
(pmt * (((1 + rate)payments) - 1)) / rate
NPV(rate,flows)
 Net Present Value. Returns the net present value of future cash flows, discounted at a periodic interest rate.
The formula used is:
SUM(flows[i] / (1 + rate)i)
It assumes that the cash flows occur at equal time intervals, with the first flow occurring at the end of the first period. If there is an initial investment at the beginning of the fist period, you must add it separately.
PMT(principal,rate,term)
 Returns the size of periodic payments necessary to pay off a loan at a periodic interest rate, over the number of payment periods specified by term. PMT assumes payments are made at the end of each period. If payments are made at the beginning of each period, divide the result by (1 + rate).
PV(pmt,rate,payments)
 Present Value. Returns the present value of a seris of payments (with each payment equal to the amount specified in pmt) that collect a specific periodic interest rate over the number of payment periods specified in payments. PV assumes payments are made at the end of each period. If payments are made at the beginning of each period, multiply the result by (1 + rate).
The formula used is:
(pmt + (1 - ((1 + rate)-payments))) / rate
SL(inv,residual,periods)
 Straight-Line Depreciation. Returns the straight-line depreciation amount per period of an inital investment, given the number of time periods over which to depreciate, and the residual value at the end of the term.
The formula used is:
(inv - residual) / periods
SOYD(inv,residual,term,period)
 Sum of the Years Digits. Returns the depreciation amount for an investment for a given period, using a sum of the years' digits depreciation. This function uses the original investment, the number of years over which to depreciate, the residual value at the end of the term, and the period for which the depreciation amount is to be calculated.
The formula used is:
(2 * (1 + term - period) * (inv - residual)) / (term * (term + 1))

Mathematical Functions

ABS(number)
 Returns the absolute value of number. E.g. ABS(-1) = 1.
ACOS(number)
 Returns the arccosine for number. The result is always between 0 and +PI, inclusive.
ASIN(number)
 Returns the arcsine for number. The result is always between -PI/2 and +PI/2, inclusive.
ATAN(number)
 Returns the arccosine for number. The result is always between -PI/2 and +PI/2, inclusive.
COS(angle)
 Returns the cosine of angle. Angle is considered to be in radians. The result is always between -1 and +1, inclusive.
COT(angle)
 Returns the cotangent of angle. Angle is considered to be in degrees. The result is always between -1 and +1, inclusive.
EXP(number)
 Returns e raised to the power number. EXP is the inverse of LN, the natural logarithm.
FRAC(number)
 Returns the absolute (positive) value of the fractional part of number.
INT(number)
 Returns the integer part of number. This part is rounded towards zero.
LN(number)
 Returns the natural logarithm, or e log, of number.
LOG(number)
 Returns the common logarithm, or 10 log, of number.
MOD(number,base)
 Returns the remainder of number divided by base. The result is between 0 and base, excluding base.
PI
 Returns 3.1415926535897932. This is the ratio of the circumference of a cicle to its radius.
RANDOM
 Returns a random number.
SIGN(number)
 Returns -1 if number is less than zero, 0 if number is equal to zero or 1 if number is greater than zero.
SIN(angle)
 Returns the sine of angle. Angle is considered to be in radians. The result is always between -1 and +1, inclusive.
SQRT(number)
 Returns the square root of number.
TAN(angle)
 Returns the tangent of angle. Angle is considered to be in radians.

Statistical Functions

AVG(list)
 Returns the average for the numerical values in list.
CEILING(number)
 Returns the smallest integral value greater than or equal to number.
COUNT(list)
 Counts the numerical values in list.
FLOOR(number)
 Returns the largest integral value smaller than or equal to number.
MAX(list)
 Returns the maximum of the values in list.
MIN(list)
 Returns the minimum of the values in list.
ROUND(number,digits)
 Returns the value of number rounded to the number of decimals specified by digits.
STDDEV(list)
 Returns the sample standard deviation of the numeric values in list.
The formula used is:
SQRT(VARIANCE(list))
SUM(list)
 Returns the sum of all numeric values in list.
VARIANCE(list)
 Returns the sample variance of all numeric values in list.
The formula used is:
Sum ((X - AVG(list))2 / n - 1)
In this formula, X is a value in the sample (list) and n is the number of values in list (COUNT(list)).

Text Functions

ASC(character)
 Returns the ascii value of character.
CHR(number)
 Returns the character with ascii value number.
LEFT(string,numchars)
 Returns a substring of string consisting of the first numchars characters of string. If numchars is bigger than the length of string then the result is the same as string.
LENGTH(string)
 Returns the number of characters string is made of.
MID(string,startchar,numchars)
 Returns a substring of string consisting of numchars characters starting at startchar. If startchar is greater then the length of string, an empty string is returned.
NUM2C(number)
 Returns number as a formatted string.
RIGHT(string,numchars)
 Returns a substring of string consisting of the last numchars characters of string. If numchars is bigger than the length of string then the result is the same as string.
TIME2C(time)
 Returns time as a formatted string.

Logical Functions

AND(list)
 Returns TRUEif all the boolean values in list are TRUE, otherwise the result is FALSE.
CHOOSE(index,list)
 Returns the indexth value from list. The first element from list is 1.
ERR
 Returns the error value !ERR. Usefull to signal errors in e.g. lookup actions.
ERROR(number)
 Returns the error value corresponding with number. A list of error values will follow in an appendix.
FALSE
 Returns the boolean value FALSE.
IF(condition,trueval,falseval)
 Returns trueval or falseval depending on the evaluation of condition.
IFERR(expression,trueval,falseval)
 If expression evaluates to an error, then trueval is returned otherwise falseval will be returned.
ISNULL(expression)
 Returns a boolean value indicating whether expression evaluates to an empty value.
ISNUM(expression)
 Returns a boolean value indicating whether expression evaluates to a numeric value.
ISTEXT(expression)
 Returns a boolean value indicating whether expression evaluates to a string value.
NA
 Returns the error !NA. Usefull while constructing a spreadsheet.
OR(list)
 Returns TRUEif at least one of the boolean values in list is TRUE, otherwise the result is FALSE.
TRUE
 Returns the boolean value TRUE.
Index, prev, next

Sum*It Manual, HTML Edition, for Release 1.0 of Sum*It.